package com.geng.dao.impl;

import com.geng.bean.Express;
import com.geng.dao.BaseExpressDao;
import com.geng.exception.DuplicateCodeException;
import com.geng.utils.DruidUtil;

import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoMysql extends DruidUtil implements BaseExpressDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day,COUNT(STATUS=0 OR NULL) data2_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day FROM EXPRESS ";
    //用于查询数据库中的所有快递信息
    private static final String SQL_FIND_ALL ="SELECT * FROM EXPRESS";
    //用于分页查询数据库中的快递信息
    private static final String SQL_FIND_LIMIT ="SELECT * FROM EXPRESS LIMIT ?,?";
    //通过取件码查询快递信息
    private static final String SQL_FIND_BY_CODE ="SELECT * FROM EXPRESS WHERE CODE=?";
    //通过快递单号查询快递信息
    private static final String SQL_FIND_BY_NUMBER ="SELECT * FROM EXPRESS WHERE NUMBER=?";
    //通过录入人的手机号码查询快递信息
    private static final String SQL_FIND_BY_SYSPHONE ="SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    //通过用户的手机号码查询快递信息
    private static final String SQL_FIND_BY_USERPHONE ="SELECT * FROM EXPRESS WHERE USERPHONE=?";
    //进行快递的录入
    private static final String SQL_INSERT ="INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,?,?,?)";
    //快递的修改
    private static final String SQL_UPDATE ="UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
    //快递的状态码的改变（取件）
    private static final String SQL_UPDATE_STATUS ="UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE = ?";
    //快递的删除
    private static final String SQL_DELETE ="DELETE FROM EXPRESS WHERE ID=?";

    /**
     * 用于查询数据库中的全部快递（总数+新增）,代取件快递（总数+新增）
     *
     * @return [{size:总数,day:新增} ,{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList();
        //不用进行参数绑定
        try {
            resultSet = query(SQL_CONSOLE,null);
            if(resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1= new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2= new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    /**
     * 用于查询所有的快递
     *
     * @param limit      是否分页的标记，true 表示分页，false 表示不分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 每页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        try {
            if(limit){
                ArrayList<Integer> params = new ArrayList();
                params.add((offset-1)*pageNumber);
                params.add(pageNumber);
                resultSet = query(SQL_FIND_LIMIT,params);
            }else
                resultSet = query(SQL_FIND_ALL,null);

            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime,status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param number 单号
     * @return 查询的快递信息，单号不存在的时候返回null
     */
    @Override
    public Express findByNumber(String number) {
        ArrayList params = new ArrayList();
        try {
            params.add(number);
            resultSet = query(SQL_FIND_BY_NUMBER,params);
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime,status, sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 根据快递取件码，查询快递信息
     *
     * @param code 取件码
     * @return 查询快递信息，取件码不存在时，返回null
     */
    @Override
    public Express findByCode(String code) {

        ArrayList params = new ArrayList();
        try {
            params.add(code);
            resultSet = query(SQL_FIND_BY_CODE,params);
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime,status, sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 根据用户手机号码，查询所有的快递信息
     *
     * @param userPhone 手机号码
     * @return 查询快递信息列表
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> data = new ArrayList<>();
        ArrayList<String> params = new ArrayList<>();
        try {
            params.add(userPhone);
            resultSet = query(SQL_FIND_BY_USERPHONE,params);
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime,status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return data;
    }

    /**
     * 根据录入人手机号码，查询录入的所有记录
     *
     * @param sysPhone 手机号码
     * @return 查询快递信息列表
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        ArrayList<String> params = new ArrayList<>();
        try {
            params.add(sysPhone);
            resultSet = query(SQL_FIND_BY_SYSPHONE,params);
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                int status = resultSet.getInt("status");
                String userPhone = resultSet.getString("userPhone");
                Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime,status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return data;
    }

    /**
     * 快递的录入
     *INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE)
     * @param newExpress
     * @return 录入的结果，true 表示成功，false 表示失败
     */
    @Override
    public boolean insert(Express newExpress) throws DuplicateCodeException {
        ArrayList params = new ArrayList();
        params.add(newExpress.getNumber());
        params.add(newExpress.getUserName());
        params.add(newExpress.getUserPhone());
        params.add(newExpress.getCompany());
        params.add(newExpress.getCode());
        params.add(newExpress.getInTime());
        params.add(newExpress.getStatus());
        params.add(newExpress.getSysPhone());
        count = update(SQL_INSERT,params);
        closeAll();
        return count>0?true:false;
    }

    /**
     * 快递的修改
     *"UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?"
     * @param id         要修改的快递id
     * @param newExpress 新的快递对象 (number,company,username,userPhone)
     * @return 修改的结果，true表示成功,false 表示失败
     */
    @Override
    public boolean update(int id, Express newExpress) throws DuplicateCodeException {
            ArrayList params = new ArrayList();
            params.add(newExpress.getNumber());
            params.add(newExpress.getUserName());
            params.add(newExpress.getCompany());
            params.add(newExpress.getStatus());
            params.add(id);
            count = update(SQL_UPDATE,params);
            closeAll();
            return count>0?true:false;
    }

    /**
     * 更改快递的状态为1,表示取件完成
     *UPDATE EXPRESS SET STATUS=1 WHERE CODE = ?
     * @param code 要修改的快递单号
     * @return 修改的结果，true表示成功，false 表示失败
     */
    @Override
    public boolean updateStatus(String code) throws DuplicateCodeException {
        ArrayList params = new ArrayList();
        params.add(code);
        count = update(SQL_UPDATE_STATUS,params);
        closeAll();
        return count>0?true:false;
    }

    /**
     * 根据id,删除单个快递信息
     *DELETE FROM EXPRESS WHERE ID=?"
     * @param id 要删除的快递id
     * @return 删除的结果，true表示成功,false 表示失败
     */
    @Override
    public boolean delete(int id) throws DuplicateCodeException {
        ArrayList params = new ArrayList();
        params.add(id);
        update(SQL_DELETE,params);
        closeAll();
        return count>0?true:false;
    }
}
